#!/home/python/.virtualenvs/equity/bin/python

import sys
sys.path.append('./')
sys.path.insert(0, "../")

import os
if not os.getenv('DJANGO_SETTINGS_MODULE'):
    os.environ['DJANGO_SETTINGS_MODULE'] = 'equity_mall.settings.dev'

import django
django.setup()

import requests, json, re, time, xlrd, pymysql, datetime, math, paramiko
from decimal import Decimal, Context, ROUND_HALF_UP
from lxml import etree
from django.conf import settings
from django.db.models import Sum, Count, Q

from activities.models import Channel
from alipaynfc.models import *
from merchants.models import Merchant
from equity_mall.utils.add_merchant import add_merchant_info
from merchants.models import MerchantEightEightActivity, MerchantEightEightWhiteTable, MerchantNFCDeviceTrade


def get_nfc_trade_statistics():
    '''获取nfc设备统计'''
    print('%s: get_nfc_trade_statistics' % time.ctime())
    today = datetime.datetime.now()
    yesterday = today + datetime.timedelta(days=-1)
    print(yesterday)
    pageSize = 1
    pageNum = 1
    url = 'https://spportal.alipay.com/serviceless/submitServiceData.json?serviceName=NDeviceDeviceDetailStatistics&serviceSpace=CATERING_BIZ_COVER&tenant=bstcateringmng'
    body = {
        "timeDim": "DAY",
        "requestType": "QUERY_DATA",
        "pageNum": pageNum,
        "pageSize": pageSize,
        "statsDates": [
            yesterday.strftime('%Y%m%d')
        ]
    }
    channel = Channel.objects.get(id=14)
    headers = {
        "Cookie": channel.cookie,
        "referer": "https://p.alipay.com/",
        "Content-Type": "application/json"
    }
    response = requests.post(url=url, data=json.dumps(body), headers=headers)
    result = response.json()
    a = 1
    if 'success' in result:
        result = result['result']['result']
        total_count = result['count']
        pageSize = 1000
        range_num = math.ceil(total_count/pageSize)
        for pageNum in range(1, range_num+1):
            a = 2
            print(pageNum)
            body['pageSize'] = pageSize
            body['pageNum'] = pageNum
            response = requests.post(url=url, data=json.dumps(body), headers=headers)
            result = response.json()['result']['result']['list']
            for i in result:
                sn = i['snNo']
                defaults = {}
                defaults['sn'] = sn
                if i['deviceType'] == '支付宝盒 N5H':
                    defaults['device_type'] = 1
                elif i['deviceType'] == '支付宝盒 N5D':
                    defaults['device_type'] = 2
                elif i['deviceType'] == '支付宝盒 N7HM':
                    defaults['device_type'] = 4
                else:
                    defaults['device_type'] = 3
                defaults['work_user'] = i['leadsWorkerName']
                defaults['work_user_uid'] = i['leadsWorkerId']
                defaults['is_register'] = i['beWorked']
                if i['workTime']:
                    defaults['register_time'] = i['workTime']
                defaults['is_lighten'] = i['beLightedUp']
                if i['lightUpTime']:
                    defaults['lighten_time'] = i['lightUpTime']
                defaults['store_name'] = i['bindingLocation']
                defaults['address'] = i['locationAddress']
                defaults['longitude'] = i['poiLongitude']
                defaults['latitude'] = i['poiLatitude']
                defaults['iotRealRightId'] = i['iotRealRightId']
                device, create = NFCDevice.objects.update_or_create(defaults=defaults, sn=sn)
                s_defaults = {}
                s_defaults['device_id'] = device.id
                s_defaults['trade_time'] = yesterday.strftime("%Y-%m-%d 00:00:00")
                s_defaults['alipay_num'] = i['alipayTransactionCount']
                s_defaults['alipay_user_num'] = i['alipayUserCount']
                s_defaults['total_amount'] = i['alipayAmount']
                s_defaults['nfc_num'] = i['nfcTransactionCount']
                s_defaults['nfc_user_num'] = i['nfcUserCount']
                s_defaults['nfc_amount'] = i['nfcAmount']
                s_defaults['longitude'] = i['poiLongitude']
                s_defaults['latitude'] = i['poiLatitude']
                if i['isTrdAmt3Device'] == '否' and i['isTrdAmt5Device'] == '0':
                    s_defaults['is_reach'] = 0
                else:
                    s_defaults['is_reach'] = 1
                s_defaults['merchant_id'] = device.merchant_id
                s_defaults['level_id'] = device.level_id
                NFCDeviceStatistics.objects.update_or_create(defaults=s_defaults, device_id=device.id, trade_time=yesterday.strftime("%Y-%m-%d 00:00:00"))
    if a == 2:
        update_nfc_device_merchant()
        update_nfc_device_data()
        update_device_statistic_nfc_num()
        get_nfc_trade()
        update_nfc_device_distinct_user_num()
    print(result)


def update_nfc_device_merchant():
    '''获取设备绑定商户数据'''
    queryset = NFCDevice.objects.filter(device_type__in=[3, 4]).filter(Q(is_lighten=1) | Q(is_register=1))
    url = 'https://manage.gdwxyf.com/internal/searchqrcode.do'
    for query in queryset:
        body = {
            "simpleCodeId": query.sn,
            "simpleCodeType": "wsy",
            "account": "gdyouzheng",
            "branchId": "2",
            "shopId": ""
        }
        response = requests.post(url=url, data=body)
        result = response.json()
        if result['returnCode'] == 'success':
            if result['list']:
                try:
                    shop_id = result['list'][0]['shopId']
                except:
                    shop_id = 'aaaaaa'
                merchant = Merchant.objects.filter(ruiyinxinMerchantID=shop_id).first()
                if merchant:
                    query.merchant_id = merchant.id
                    query.level_id = merchant.level_id
                    query.save()
                else:
                    merchant = add_merchant_info(result['list'][0]['shop_account'])
                    if not merchant['merchant']:
                        print(result['list'][0]['shop_account'])
                    else:
                        query.merchant_id = merchant['merchant'].id
                        query.level_id = merchant['merchant'].level_id
                        query.save()
                    print(result['list'][0]['shop_account'])


def check_merchant_nfc_trade(shopId, start, end):
    '''校验商户nfc交易'''
    url = 'https://manage.gdwxyf.com/internal/searchorders.do'
    body = {
        "account": "gdyouzheng",
        "branchId": "2",
        "shopId": shopId,
        "channel": "all",
        "orderStatus": "all",
        "orderType": "all",
        "agentId": "all",
        "start": start,
        "end": end
    }
    try:
        response = requests.post(url=url, data=body)
        result = response.json()['showList']
        for i in result:
            if i['totalNormal'] >= 4:
                return 1
        return 0
    except:
        return 0


def get_login_toekn():
    '''获取智慧账单有效token'''
    url = 'https://bill.xiangma365.com/index.php/base/login/index'
    body = {
        "account": "gdyouzheng",
        "pw": "youzhenggd"
    }
    response = requests.post(url=url, data=body)
    return response.json()['data']


def update_device_statistic_nfc_num():
    '''更新设备nfc交易笔数'''
    today = datetime.datetime.now() - datetime.timedelta(days=1)
    url = 'https://bill.xiangma365.com/index.php/api/big_data/list'
    token = get_login_toekn()
    body = {
        "search": '{"startDate":"' + today.strftime('%Y-%m-%d') + '","endDate":"' + today.strftime('%Y-%m-%d') + '","sheng_name":"2","pageNum":1,"pageSize":1}',
        "jobType": "TouchDeviceTran",
        "token": token
    }
    response = requests.post(url=url, data=body)
    result = response.json()
    if result['code'] != '200':
        return result['msg']
    totalNum = result['data']['list']['totalNum']
    pageSize = 1000
    range_num = math.ceil(totalNum / pageSize)
    list_data = []
    for pageNum in range(1, range_num + 1):
        body = {
            "search": '{"startDate":"' + today.strftime('%Y-%m-%d') + '","endDate":"' + today.strftime('%Y-%m-%d') + '","sheng_name":"2","pageNum":' + str(pageNum) + ',"pageSize":' + str(pageSize) + '}',
            "jobType": "TouchDeviceTran",
            "token": token
        }
        response = requests.post(url=url, data=body)
        result = response.json()
        if result['code'] != '200':
            return result['msg']
        list_data += result['data']['list']['rows']
    for i in list_data:
        NFCDeviceStatistics.objects.filter(device__sn=i['device_id'], trade_time=today.strftime('%Y-%m-%d 00:00:00')).update(nfc_amount3_num=i['nfc_3u_cnt'])
    print(response.json())


def statistics_white_merchant():
    '''统计得奖商户'''
    today = datetime.datetime.now() + datetime.timedelta(hours=12)
    check_activity = MerchantEightEightActivity.objects.filter(activity_start_time__lte=today, activity_end_time__gte=today, activity_type=2).first()
    trade_time_begin = (check_activity.activity_start_time - datetime.timedelta(days=7)).strftime("%Y-%m-%d 00:00:00")
    trade_time_end = (check_activity.activity_end_time - datetime.timedelta(days=7)).strftime("%Y-%m-%d 23:59:59")
    print(trade_time_begin, trade_time_end)
    conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                           port=settings.DATABASES['default']['PORT'],
                           database=settings.DATABASES['default']['NAME'],
                           user=settings.DATABASES['default']['USER'],
                           password=settings.DATABASES['default']['PASSWORD'])
    cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
    sql = f'''SELECT * FROM (SELECT b.*,c.merchant_id FROM (SELECT * FROM (SELECT device_id,SUM(nfc_amount3_num) AS nfc_amount3_num,GROUP_CONCAT(DISTINCT DATE_FORMAT(trade_time,'%Y-%m-%d') ORDER BY trade_time)AS trade_time FROM tb_nfc_device_statistics WHERE trade_time>="{trade_time_begin}" AND trade_time<="{trade_time_end}" AND nfc_amount3_num>0 GROUP BY device_id HAVING SUM(nfc_amount3_num)>=30)AS a WHERE CHARACTER_LENGTH(a.trade_time)>=32)AS b LEFT JOIN (SELECT id,merchant_id FROM tb_nfc_device)AS c ON b.device_id=c.id)AS d LEFT JOIN (SELECT id,wyfMerchantID,level_id FROM tb_merchant)AS e ON d.merchant_id=e.id;'''
    cursor.execute(sql)
    rows = cursor.fetchall()
    cursor.close()
    conn.close()
    for row in rows:
        defaults = {}
        defaults['business_no'] = row['wyfMerchantID']
        defaults['activity_id'] = check_activity.id
        defaults['is_win_type'] = 2
        defaults['level_id'] = row['level_id']
        MerchantEightEightWhiteTable.objects.get_or_create(defaults=defaults, activity_id=check_activity.id, business_no=row['wyfMerchantID'])
    return rows


def update_nfc_device_data():
    '''更新设备动销数据'''
    statistics_time = NFCDeviceStatistics.objects.all().last().trade_time + datetime.timedelta(days=1)
    queryset = NFCDevice.objects.filter(is_lighten=1)
    for query in queryset:
        lighten_time = query.lighten_time
        end_lighten_time = lighten_time + datetime.timedelta(days=29)
        remain_days = 29 - (statistics_time - lighten_time).days
        query.remain_days = remain_days if remain_days>0 else 0
        statistics_set = NFCDeviceStatistics.objects.filter(device_id=query.id, trade_time__lte=end_lighten_time)
        alipay_num = 0
        total_amount = 0
        nfc_num = 0
        nfc_amount = 0
        reach_days = 0
        reach_list = []
        for statistics in statistics_set:
            alipay_num += statistics.alipay_num
            total_amount += statistics.total_amount
            nfc_num += statistics.nfc_num
            nfc_amount += statistics.nfc_amount
            reach_days += statistics.is_reach
            if statistics.is_reach == 1:
                reach_list.append(f'{statistics.trade_time.strftime("%m.%d")}:{statistics.nfc_num}')
        query.alipay_num = alipay_num
        query.total_amount = total_amount
        query.nfc_num = nfc_num
        query.nfc_amount = nfc_amount
        query.reach_days = reach_days
        query.reach_list = json.dumps(reach_list)
        if reach_days >= 20:
            query.reach_status = 1
        query.save()


def update_nfc_device_distinct_user_num():
    today = datetime.datetime.now()+ datetime.timedelta(days=1)
    queryset = NFCDevice.objects.filter(is_lighten=1)
    for query in queryset:
        lighten_time = query.lighten_time
        end_lighten_time = lighten_time + datetime.timedelta(days=29)
        if end_lighten_time < today:
            continue
        a = MerchantNFCDeviceTrade.objects.filter(device_id=query.id, trade_time__lte=end_lighten_time, smid__isnull=False, pay_type=1).exclude(smid='')
        if a:
            print(query.id)
            print(a.values('smid').annotate(Count('smid')))
            distinct_user_num = a.values('smid').annotate(Count('smid')).count()
            query.distinct_user_num = distinct_user_num
            if distinct_user_num >= 20:
                query.distinct_user_status = 1
                if query.reach_status == 1:
                    query.rewards_status = 1
            query.save()


def get_nfc_trade():
    '''抓取碰一下交易数据'''
    today = datetime.datetime.now()
    yesterday = today - datetime.timedelta(days=1)
    host = '47.97.196.250'
    port = 5022
    username = 'pengyixia'
    password = 'pengyixia123456'
    remote = '/'
    sf = paramiko.Transport((host, port))
    sf.connect(username=username, password=password)
    sftp = paramiko.SFTPClient.from_transport(sf)
    local = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/')
    file_name = f'touch_order_detail_{yesterday.strftime("%Y%m%d")}.csv'
    is_update = 0
    if os.path.isdir(local):
        dir_list = sftp.listdir(remote + f'upload/')
        for f in dir_list:
            if f == file_name:
                is_update = 1
                sftp.get(os.path.join(remote + f'upload/' + f), os.path.join(local + f))
    if is_update:
        with open(os.path.join(local + file_name), 'r', encoding='gbk') as f:
            result = f.readlines()
        print(result)
    if is_update:
        with open(os.path.join(local + file_name), 'r', encoding='gbk') as f:
            result = f.readlines()
        print(result)
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = 'INSERT INTO tb_merchant_nfc_device_trade(sn,device_type,store_name,province,city,district,address,transaction_id,trade_type,pay_type,total_amount,trade_amount,trade_time,shop_id,smid) VALUES '
            for i in result:
                i = i.replace('\n', '').split(',')
                if i[9] == '碰一下':
                    rowValues = [i[2], i[3], i[0], i[4], i[5], i[6], i[7], i[8].replace("'", ''), "", 1, i[10], i[10], i[11].replace("'", ''), i[1], i[12].replace("'", '')]
                    sql += str(tuple(rowValues))
                    sql += ','
            sql = sql[:-1]
            sql += ';'
            cursor.execute(sql)
            conn.commit()
            sql = f'''UPDATE tb_merchant_nfc_device_trade,tb_nfc_device SET tb_merchant_nfc_device_trade.device_id=tb_nfc_device.id WHERE tb_merchant_nfc_device_trade.trade_time>="{yesterday.strftime('%Y-%m-%d 00:00:00')}" AND tb_merchant_nfc_device_trade.trade_time<="{yesterday.strftime('%Y-%m-%d 23:59:59')}" AND tb_merchant_nfc_device_trade.sn=tb_nfc_device.sn;'''
            cursor.execute(sql)
            conn.commit()
            sql = f'''UPDATE tb_merchant_nfc_device_trade,tb_merchant SET tb_merchant_nfc_device_trade.merchant_id=tb_merchant.id,tb_merchant_nfc_device_trade.level_id=tb_merchant.level_id WHERE tb_merchant_nfc_device_trade.trade_time>="{yesterday.strftime('%Y-%m-%d 00:00:00')}" AND tb_merchant_nfc_device_trade.trade_time<="{yesterday.strftime('%Y-%m-%d 23:59:59')}" AND tb_merchant_nfc_device_trade.shop_id=tb_merchant.wyfMerchantID;'''
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
            conn.rollback()
        cursor.close()
        conn.close()
    os.remove(os.path.join(local + file_name))


def get_eight_activity_white_table():
    '''获得认证有礼白名单'''
    today = datetime.datetime.now()
    week = today.weekday()
    # if week != 0:
    #     return '不再更新时间内'
    yesterday = today - datetime.timedelta(days=1)
    host = '47.97.196.250'
    port = 5022
    username = 'pengyixia'
    password = 'pengyixia123456'
    remote = '/'
    sf = paramiko.Transport((host, port))
    sf.connect(username=username, password=password)
    sftp = paramiko.SFTPClient.from_transport(sf)
    local = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), f'static/')
    file_name = f'gd_change_channel_{yesterday.strftime("%Y%m%d")}.csv'
    is_update = 0
    if os.path.isdir(local):
        dir_list = sftp.listdir(remote + f'upload/')
        for f in dir_list:
            if f == file_name:
                is_update = 1
                sftp.get(os.path.join(remote + f'upload/' + f), os.path.join(local + f))
    if is_update:
        with open(os.path.join(local + file_name), 'r', encoding='gbk') as f:
            result = f.readlines()
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = 'SELECT business_no FROM tb_merchant_eigth_eight_draw WHERE activity_id IN (86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,110,111,112);'
        cursor.execute(sql)
        rows = cursor.fetchall()
        business_no_list = []
        for row in rows:
            business_no_list.append(row['business_no'])
        delete_sql = 'DELETE FROM tb_merchant_eigth_eight_white_table WHERE activity_id IN (86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,110,111,112) AND business_no NOT IN (SELECT business_no FROM tb_merchant_eigth_eight_draw WHERE activity_id IN (86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,110,111,112));'
        cursor.execute(delete_sql)
        conn.commit()
        result = result[1:]
        insert_sql = 'INSERT INTO tb_merchant_eigth_eight_white_table(business_no,activity_id,is_win_type) VALUES '
        for i in result:
            i = i.replace('\n', '').split(',')
            business_no = i[1]
            tag = i[3]
            city = i[0]
            if city == '广东邮政':
                if tag == '高频':
                    activity_id = 88
                elif tag == '中频':
                    activity_id = 87
                else:
                    activity_id = 86
            elif city == '江苏邮政':
                if tag == '高频':
                    activity_id = 91
                elif tag == '中频':
                    activity_id = 90
                else:
                    activity_id = 89
            elif city == '广西邮政':
                if tag == '高频':
                    activity_id = 94
                elif tag == '中频':
                    activity_id = 93
                else:
                    activity_id = 92
            elif city == '湖南邮政':
                if tag == '高频':
                    activity_id = 97
                elif tag == '中频':
                    activity_id = 96
                else:
                    activity_id = 95
            elif city == '福建邮政':
                if tag == '高频':
                    activity_id = 100
                elif tag == '中频':
                    activity_id = 99
                else:
                    activity_id = 98
            elif city == '吉林邮政':
                if tag == '高频':
                    activity_id = 103
                elif tag == '中频':
                    activity_id = 102
                else:
                    activity_id = 101
            elif city == '海南邮政':
                if tag == '高频':
                    activity_id = 106
                elif tag == '中频':
                    activity_id = 105
                else:
                    activity_id = 104
            elif city == '陕西邮政':
                if tag == '高频':
                    activity_id = 112
                elif tag == '中频':
                    activity_id = 111
                else:
                    activity_id = 110
            else:
                continue
            if business_no not in business_no_list:
                rowValues = [business_no, activity_id, 2]
                insert_sql += str(tuple(rowValues))
                insert_sql += ','
        insert_sql = insert_sql[:-1]
        insert_sql += ';'
        cursor.execute(insert_sql)
        conn.commit()
        cursor.close()
        conn.close()
    os.remove(os.path.join(local + file_name))


if __name__ == '__main__':
    # print(check_merchant_nfc_trade('6032746106', '2024-11-22 10:00:00', '2024-11-22 12:59:59'))
    # statistics_white_merchant()
    print(get_eight_activity_white_table())